
PRINT '*********************************************************************'
PRINT '*** INSTALLATION STARTED ' + CONVERT(VARCHAR(255),GETDATE(),120) + ' ***'
PRINT '*********************************************************************'

USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------

--Enable CLR Integration if is not enabled
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'show advanced options' AND [value] = 1) BEGIN 
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	PRINT '*** Enable SQL Advanced Options - Completed with SUCCESS ***'
END

IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'clr enabled' AND [value] = 1) BEGIN 
	EXEC sp_configure 'clr enabled', 1
	RECONFIGURE
	PRINT '*** Enable CLR - Completed with SUCCESS ***'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_levenshtein' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].[fn_levenshtein]
	PRINT '*** DROP FUNCTION [clr].[fn_levenshtein] - Completed with SUCCESS ***'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_damerau_levenshtein' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].[fn_damerau_levenshtein]
	PRINT '*** DROP FUNCTION [clr].[fn_damerau_levenshtein] - Completed with SUCCESS ***'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_jaro_winkler' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].[fn_jaro_winkler]
	PRINT '*** DROP FUNCTION [clr].[fn_jaro_winkler] - Completed with SUCCESS ***'
END


--Drop the assembly if it exist
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrFuzzy') BEGIN 
	DROP ASSEMBLY [ClrFuzzy]
	PRINT '*** DROP Assembly ClrFuzzy - Completed with SUCCESS ***'
END

-- Create the assembly if it does not exists
CREATE ASSEMBLY [ClrFuzzy] 
--FROM 'C:\Program Files\Microsoft SQL Server\100\CLR\ClrFuzzy.dll' 
-- testing only
FROM 'D:\Projects\SqlLib\Project\CLR\Fuzzy\bin\Debug\ClrFuzzy.dll' 
WITH PERMISSION_SET = SAFE
	
PRINT '*** CREATE Assembly ClrFuzzy - Completed with SUCCESS ***'


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			[clr].[fn_levenshtein]
Type:			Custom CLR UDF
Owner:					
Description:	DDL function, returns the edit distance between 2 strings  
Parameters:		@source
				@target
				@ignore_case 
Dependens on:  	ClrObjDef.dll
Usage:			SELECT clr.fn_levenshtein('Trans','Trend',1)	
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_levenshtein&referringTitle=Documentation	
Reference:		http://en.wikipedia.org/wiki/Levenshtein_distance	
Version:		20121106
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		11/06/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].[fn_levenshtein] 
	( @source NVARCHAR(4000)
	 ,@target NVARCHAR(4000) 
	 ,@ignore_case BIT = 1
	 )
RETURNS BIGINT	
AS EXTERNAL NAME ClrFuzzy.Levenshtein.EditDistance

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_levenshtein' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '*** CREATE FUNCTION [clr].[fn_levenshtein] - Completed with SUCCESS ***'
END

ELSE
	PRINT '*** CREATE FUNCTION [clr].[fn_levenshtein] - Completed with ERROR ***'

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

GO


/*================================================================================================
Name:			[clr].[fn_damerau_levenshtein]
Type:			Custom CLR UDF
Owner:					
Description:	DDL function, returns the edit distance between 2 strings 
Parameters:		@source
				@target
				@ignore_case    
Dependens on:  	ClrObjDef.dll
Usage:			SELECT clr.fn_damerau_levenshtein('Trans','Tranz',1)	
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_damerau_levenshtein&referringTitle=Documentation	
Reference:		http://en.wikipedia.org/wiki/Damerau%E2%80%93Levenshtein_distance	
Version:		20121106
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		11/06/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].[fn_damerau_levenshtein] 
	( @source NVARCHAR(4000)
	 ,@target NVARCHAR(4000) 
	 ,@ignore_case BIT = 1
	 )
RETURNS BIGINT	
AS EXTERNAL NAME ClrFuzzy.DamerauLevenshtein.EditDistance

GO
---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_damerau_levenshtein' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '*** CREATE FUNCTION [clr].[fn_damerau_levenshtein] - Completed with SUCCESS ***'
END

ELSE
	PRINT '*** CREATE FUNCTION [clr].[fn_damerau_levenshtein] - Completed with ERROR ***'

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

GO

/*================================================================================================
Name:			[clr].[fn_jaro_winkler]
Type:			Custom CLR UDF
Owner:					
Description:	DDL function, returns the edit distance between 2 strings 
Parameters:		@source
				@target
Dependens on:  	ClrObjDef.dll
Usage:			SELECT clr.fn_jaro_winkler('Trans','Tranz',1)	
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_jaro_winkler&referringTitle=Documentation	
Reference:		http://en.wikipedia.org/wiki/Damerau%E2%80%93Levenshtein_distance	
Version:		20121106
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		11/06/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION [clr].[fn_jaro_winkler] 
	( @source NVARCHAR(4000)
	 ,@target NVARCHAR(4000) 
	 )
RETURNS FLOAT	
AS EXTERNAL NAME ClrFuzzy.JaroWinkler.SimilarityScore

GO
---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_jaro_winkler' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '*** CREATE FUNCTION [clr].[fn_jaro_winkler] - Completed with SUCCESS ***'
END

ELSE
	PRINT '*** CREATE FUNCTION [clr].[fn_jaro_winkler] - Completed with ERROR ***'

----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

GO 

PRINT '*********************************************************************'
PRINT '*** INSTALLATION COMPLETE ' + CONVERT(VARCHAR(255),GETDATE(),120) + ' ***'
